USE [db2]
GO

CREATE TABLE [dbo].[pokoje](
	[id] int identity(1,1) PRIMARY KEY,
	[nazwa] varchar(20) NOT NULL,
)
ON [PRIMARY]
GO


CREATE TABLE [dbo].[okresy](
	[id] int identity(1,1) PRIMARY KEY,
	[nazwa] [varchar](20) NOT NULL,
	[data_od] [datetime] NOT NULL,
	[data_do] [datetime] NOT NULL,
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[ceny](
	[id] int identity(1,1) PRIMARY KEY,
	[okresy_id] [int] NOT NULL,
	[pokoje_id] [int] NOT NULL,
	[cena] [money] NOT NULL,
)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[ceny] 
ADD CONSTRAINT [ceny_fk2] FOREIGN KEY ([okresy_id]) 
  REFERENCES [dbo].[okresy] ([id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[ceny] 
ADD CONSTRAINT [ceny_fk1] FOREIGN KEY ([pokoje_id]) 
  REFERENCES [dbo].[pokoje] ([id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO


CREATE PROCEDURE pokazKosztPokoju
@pokoju_id int,
@pobyt_od datetime,
@pobyt_do datetime

AS

SELECT  p.id as id_pokoju, p.nazwa as nazwa_pok, (case when @pobyt_od < o.data_od then o.data_od else @pobyt_od end) as pobyt_od, (case when @pobyt_do > o.data_do then o.data_do else @pobyt_do end) as pobyt_do, 
((case when @pobyt_do > o.data_do then o.data_do else @pobyt_do end)-(case when @pobyt_od < o.data_od then o.data_od else @pobyt_od end)) as ilosc_dni,  c.cena,
( CAST(((case when @pobyt_do > o.data_do then o.data_do else @pobyt_do end)-(case when @pobyt_od < o.data_od then o.data_od else @pobyt_od end)) AS FLOAT)) as wartosc
FROM (ceny c INNER JOIN pokoje p ON (c.pokoje_id = p.id)) INNER JOIN okresy o ON (c.okresy_id = o.id) 
WHERE ((case when @pobyt_do > o.data_do then o.data_do else @pobyt_do end)-(case when @pobyt_od < o.data_od then o.data_od else @pobyt_od end))>0

Go
